﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
    public class SellGoodsDAL
    {

        public string connecttion = "SERVER=localhost;" +
                                    "DATABASE=sellingmobilemanagement;" +
                                    "UID=root;" +
                                    "PASSWORD= 515154; ";

        #region[SellGoods_SelectALL]
        public List<DTO.SellGoodsDTO> SellGoods_SelectALL()
        {
            List<DTO.SellGoodsDTO> sadall = new List<DTO.SellGoodsDTO>();

            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAllSellGoods", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.SellGoodsDTO sad = new DTO.SellGoodsDTO();
                sad.SellID = dr.GetInt32(0);
                sad.Date = dr.GetDateTime(1);
                sad.TotalSellCost = dr.GetInt32(2);
                sad.CustomerID = dr.GetInt32(3);
                sad.AccountID = dr.GetInt32(4);
                sadall.Add(sad);
            }
            dr.Close();
            con.Close();

            return sadall;
        }
        #endregion

        #region [SellGoods_Insert]
        public bool SellGoods_Insert(DTO.SellGoodsDTO sad)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("AddSellGoods", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@date", sad.Date));
            cmd.Parameters.Add(new MySqlParameter("@cost", sad.TotalSellCost));
            cmd.Parameters.Add(new MySqlParameter("@cusid", sad.CustomerID));
            cmd.Parameters.Add(new MySqlParameter("@accid", sad.AccountID));
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion


        #region [SellGoods_InsertReport]
        public bool SellGoods_InsertReport(DTO.SellGoodsDTO sad)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("insertReport", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@SellID", sad.SellID));
            cmd.Parameters.Add(new MySqlParameter("@CustomerID", sad.CustomerID));
            cmd.Parameters.Add(new MySqlParameter("@Date", sad.Date));
            cmd.Parameters.Add(new MySqlParameter("@Total", sad.TotalSellCost));
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region[SellGoods_SelectByDate]
        public List<DTO.SellGoodsDTO> SellGoods_SelectByDate(String date, String year)
        {
            List<DTO.SellGoodsDTO> sadbydate = new List<DTO.SellGoodsDTO>();

            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectSellGoodsByDate", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@date", date);
            cmd.Parameters.AddWithValue("@year", year);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.SellGoodsDTO sad = new DTO.SellGoodsDTO();
                sad.SellID = dr.GetInt32(0);
                sad.Date = dr.GetDateTime(1);
                sad.TotalSellCost = dr.GetInt32(2);
                sad.CustomerID = dr.GetInt32(3);
                sad.AccountID = dr.GetInt32(4);
                sadbydate.Add(sad);
            }
            dr.Close();
            con.Close();

            return sadbydate;
        }
        #endregion

        #region[SellGoods_SelectByDate1]
        public DataTable SellGoods_SelectByDate1(String date)
        {
            List<DTO.SellGoodsDTO> sadbydate = new List<DTO.SellGoodsDTO>();
            DataTable a = new DataTable();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectSellGoodsByDate", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@date", date);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.SellGoodsDTO sad = new DTO.SellGoodsDTO();
                sad.SellID = dr.GetInt32(0);
                sad.Date = dr.GetDateTime(1);
                sad.TotalSellCost = dr.GetInt32(2);
                sad.CustomerID = dr.GetInt32(3);
                sad.AccountID = dr.GetInt32(4);
                sadbydate.Add(sad);
            }
            dr.Close();
            con.Close();
            a.Load(dr);

            return a;
        }
        #endregion

        #region[SellGoods_DeleteReport]
        public bool SellGoods_DeleteReport()
        {
            List<DTO.SellGoodsDTO> sadbydate = new List<DTO.SellGoodsDTO>();

            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = "DELETE FROM Report";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteReader();
            con.Close();

            return true;
        }
        #endregion
    }
}
